
package com.contactmodule.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import util.ConnectDB;
import util.UtilDAO;
import util.VskPage;

import com.contactmodule.bean.TbDepart;
public class TbDepartDAO {
    private Connection conn = null;
    private PreparedStatement pt = null;
    private ResultSet rs = null;
    private boolean flag = false;
    private String sql = "";
    private TbDepart tbDepart=null;
    private List<TbDepart> tbDepartList=null;
    private Map<Integer,TbDepart> mapDepart=null;
    private Statement st = null;

    public boolean add(TbDepart inTbDepart){
        try{
        conn=ConnectDB.getSqlServerConnection();
        sql="insert into Tb_Depart(DepartName,ParentDepartId,DepartMemo) values(?,?,?)";
        pt = conn.prepareStatement(sql);
        pt.setString(1,inTbDepart.getDepartName());
        pt.setInt(2, inTbDepart.getParentDepartId());
        pt.setString(3,inTbDepart.getDepartMemo());
        if (pt.executeUpdate() > 0) flag=true;
        }catch(Exception e){
            e.printStackTrace();
        }finally {
            ConnectDB.closeSqlConnection();
        }
        return flag;
    }
    public boolean del(int inDepartId){
        return UtilDAO.del("Tb_Depart", "DepartId", inDepartId);
    }
    public boolean edit(TbDepart inTbDepart){
        try{
        conn=ConnectDB.getSqlServerConnection();
        sql="update Tb_Depart set  DepartName=?,DepartMemo=?,CreateTime=getdate() where DepartId=?";
        pt = conn.prepareStatement(sql);
        pt.setString(1,inTbDepart.getDepartName());
        pt.setString(2,inTbDepart.getDepartMemo());
        pt.setInt(3,inTbDepart.getDepartId());
        if (pt.executeUpdate() > 0) flag=true;
        }catch(Exception e){
            e.printStackTrace();
        }finally {
        	ConnectDB.closeSqlConnection();
        }
        return flag;
    }
    public TbDepart getTbDepartByDepartId(int inDepartId) {
        tbDepart=null;
        try{
            conn = ConnectDB.getSqlServerConnection();
            sql = "select * from Tb_Depart where DepartId=? ";
            pt = conn.prepareStatement(sql);
            pt.setInt(1,inDepartId);
            rs = pt.executeQuery();
            if(rs.next()) {
                tbDepart = getTbDepart(rs);
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
        	ConnectDB.closeSqlConnection();
        }
        return tbDepart;
    }
    public List<TbDepart> getTbDepartAll() {
        tbDepartList=new ArrayList<TbDepart>();
        try{
            conn = ConnectDB.getSqlServerConnection();
            sql = "select * from Tb_Depart";
            pt = conn.prepareStatement(sql);
            rs = pt.executeQuery();
            while (rs.next()) {
                tbDepartList.add(getTbDepart(rs));
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
        	ConnectDB.closeSqlConnection();
        }
        return tbDepartList;
    }
    public List<TbDepart> getTbDepartAll(VskPage vp) {
        tbDepartList=new ArrayList<TbDepart>();
        try{
            conn = ConnectDB.getSqlServerConnection();
            sql = "select * from Tb_Depart";
            vp.setAllCountSql(sql);
            vp.getAllRecordCount(conn, vp.getAllCountSql());
            pt = conn.prepareStatement(vp.processPageSql( "Tb_Depart", "DepartId"));
            rs = pt.executeQuery();
            while (rs.next()) {
                tbDepartList.add(getTbDepart(rs));
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
        	ConnectDB.closeSqlConnection();
        }
        return tbDepartList;
    }
    public TbDepart getTbDepart(ResultSet rs) throws Exception{
        TbDepart tbDepart = new TbDepart();
        tbDepart.setDepartId(rs.getInt("DepartId"));
        tbDepart.setDepartName(rs.getString("DepartName"));
        tbDepart.setParentDepartId(rs.getInt("ParentDepartId"));
        tbDepart.setDepartMemo(rs.getString("DepartMemo"));
        tbDepart.setCreateTime(rs.getString("CreateTime"));
        return tbDepart;
    }
    /**
     * 根据父类ID得到相应的部门列表
     *@author qingyu zhang
     *@function:
     * @param parDepartId
     * @return
     *2011-6-15
     *上午09:29:45
     *ProjectManagerT
     *com.departmodule.dao
     *List<TbDepart>
     */
    public List<TbDepart> getDepartListByParId(int parDepartId){
    	tbDepartList = new ArrayList<TbDepart>();
    	  try {
			conn = ConnectDB.getSqlServerConnection();
			sql="select * from Tb_Depart where ParentDepartId ="+parDepartId;
			pt = conn.prepareStatement(sql);
			rs = pt.executeQuery();
			while(rs.next()){
				tbDepartList.add(this.getTbDepart(rs));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
        	ConnectDB.closeSqlConnection();
        }
        return tbDepartList;
    }
    public Map<Integer, TbDepart> getDepart(){
    	mapDepart = new HashMap<Integer,TbDepart>();
    	try{
            conn = ConnectDB.getSqlServerConnection();
            sql = "select * from Tb_Depart";
            pt = conn.prepareStatement(sql);
            rs = pt.executeQuery();
            while (rs.next()) {
            	mapDepart.put(getTbDepart(rs).getDepartId(), getTbDepart(rs));
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
        	ConnectDB.closeSqlConnection();
        }
    	return mapDepart;
    }
    public void inserData(){
    	try {
			conn=ConnectDB.getSqlServerConnection();
			st = conn.createStatement();
			for(int i=0;i<500;i++){
				String hql="insert into tb_Depart (DepartName,ParentDepartId,DepartMemo) values('部门"+i+"',0,'部门"+i+"')";
				System.out.println("hql==="+hql);
				st.addBatch(hql);
			}
			st.executeBatch();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
        	ConnectDB.closeSqlConnection();
        }
    }
    /* vsk code start 20110616 */
    public boolean isExistSon(int inParDepartId){
    	try {
    		conn=ConnectDB.getSqlServerConnection();
    		sql = "select count(*) from tb_Depart where ParentDepartId=? ";
            pt = conn.prepareStatement(sql);
            pt.setInt(1,inParDepartId);
            rs = pt.executeQuery();
           if(rs.next())flag=rs.getInt(1)==0?false:true;
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
        	ConnectDB.closeSqlConnection();
        }
		return flag;
    	
    }
    
    public int getTbDepart(int inParDepartId,List<TbDepart> lastList){
    	TbDepartDAO dao=new TbDepartDAO();
    	if(dao.isExistSon(inParDepartId)){
    		int tempId=-2;
    		List<TbDepart> tdlist=dao.getDepartListByParId(inParDepartId);
    		for(int i=0;i<tdlist.size();i++){
    			TbDepart td=tdlist.get(i);
    			lastList.add(td);
    			if(dao.getTbDepart(td.getDepartId(),lastList)==-2 || dao.isExistSon(td.getDepartId()))
    				tempId=td.getDepartId();
    			else
    				tempId=dao.getTbDepart(td.getDepartId(),lastList);
    			
    		}
    		return tempId;
    	}else
    		return -2;
    }
   /**
    * 得到部门表中最大ID值
    *@author qingyu zhang
    *@function:
    * @return
    *2011-6-17
    *下午03:05:06
    *ProjectManagerT
    *com.departmodule.dao
    *int
    */
    public int  getMaxDepartId(){
    	int maxDepartId=0;
    	try {
			conn=ConnectDB.getSqlServerConnection();
			sql="select max(DepartId) maxDepartId from Tb_Depart ";
			pt = conn.prepareStatement(sql);
			rs = pt.executeQuery();
			if(rs.next()){
				maxDepartId = rs.getInt("maxDepartId");
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
        	ConnectDB.closeSqlConnection();
        }
		return maxDepartId;
    }
    public static void main(String[] args) {
    	TbDepartDAO tddao=new TbDepartDAO();
    	System.out.println("开始时间"+System.currentTimeMillis());
		List<TbDepart> tdlist=tddao.getDepartListByParId(0);
		System.out.println("开始时间TTTTTTT"+System.currentTimeMillis());
		for(int i=0;i<tdlist.size();i++){
			TbDepart td=tdlist.get(i);
			if(tddao.isExistSon(td.getDepartId())){
				tddao.getTbDepart(td.getDepartId(), tdlist);
			}
		}
		System.out.println("结束时间TTTTTTT"+System.currentTimeMillis());
		for(int i=0;i<tdlist.size();i++){
			TbDepart td=tdlist.get(i);
			System.out.println(i+"|"+td.getDepartId()+"||"+td.getDepartName());
		}
	}
}
